package domain;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.text.SimpleDateFormat;
import dal.ConnectionManager;


public class Person {

	/**Every person has a unique id */
	private Integer id;
	/**Name of the person*/
	private String name;
	/**Phone number of the person*/
	private String phone;
	/**Address of the person*/
	private String address;
	/**Salary of the person*/
	private Integer salary;
	/**The position of the person (employee, consultant)*/
	private Integer type;
	/**The role of the person in a project*/
	private Integer roleId;
	/**The resourceId of the resource that person owns*/
	private Integer resourceId;
	SimpleDateFormat format = new SimpleDateFormat("MM/dd/yyyy");
	ConnectionManager mgr = ConnectionManager.getInstance();
	Connection con = mgr.getConnection();
	Statement st;


	/**
	 * Empty constructor of Person
	 */
	public Person() {
		// TODO Auto-generated constructor stub
	}
	
	
	/**
	 * Constructor of Person
	 * @param name of the person
	 * @param salary of the person
	 * @param address of the person
	 * @param phone of the person
	 * @param roleId of the person
	 */
	public Person(String name, String phone,
			String address, Integer salary, Integer type, Integer roleId,
			Integer resourceId) {
		super();
		this.name = name;
		this.phone = phone;
		this.address = address;
		this.salary = salary;
		this.type = type;
		this.roleId = roleId;
		this.resourceId = resourceId;
	}


	
	/**
	 * Add new person 
	 * @param myPerson
	 */
	public void addPerson(Person myPerson)

	{
		
		try{
			Statement st = con.createStatement();
		    st.executeUpdate("insert into person(name,phone,address,salary,type,roleId,resourceId) values ('"+myPerson.getName()+"', '"+myPerson.getPhone()+"', '"+myPerson.getAddress()+"', '"+myPerson.getSalary()+"','"+myPerson.getType()+"','"+myPerson.getRoleId()+"','"+myPerson.getResourceId()+"') ");
		    ResultSet rs = st.executeQuery("select id from resource where name='"+myPerson.getName()+"'");
		    while(rs.next())
			{
				myPerson.setId(rs.getInt(1));
			}
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	} 
	
	/**
	 * Delete person
	 * @param myPerson
	 */
	public void deletePerson(Person myPerson){
		
		try{
			Statement st = con.createStatement();
			st.executeUpdate("Delete from employee where id="+myPerson.getId());
		} catch(SQLException e){
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * Update the information of person
	 * @param myPerson
	 */
	public void updatePerson(Person myPerson){
		try{
			Statement st = con.createStatement();
			st.executeUpdate("Update employee set name='"+myPerson.getName()+"', phone='"+myPerson.getPhone()+"', address='"+myPerson.getAddress()+"', salary='"+myPerson.getSalary()+"', type='"+myPerson.getType()+"', roleId='"+myPerson.getRoleId()+"', resourceId='"+myPerson.getResourceId()+"' ");
		} catch(SQLException e){
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	/**
	 * Select all the persons from database
	 * @return
	 */

	public ResultSet selectPerson()
	{
		ResultSet rs = null;
		try {
			Statement st = con.createStatement();
			rs = st.executeQuery("select * from person");
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		
		return rs;
	}

	/**
	 * Select a defined person from database according to the id
	 * @param ID
	 * @return
	 */
	public Person selectPersonById(Integer ID)
	{
		
		try {
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery("select * from person where id="+ID);
			Person myPerson = new Person();
			while(rs.next())
			{
				myPerson.setId(rs.getInt(1));
				myPerson.setName(rs.getString(2));
				myPerson.setPhone(rs.getString(3));
				myPerson.setAddress(rs.getString(4));
				myPerson.setSalary(rs.getInt(5));
				myPerson.setType(rs.getInt(6));
				myPerson.setRoleId(rs.getInt(7));
				myPerson.setResourceId(rs.getInt(8));
				
			}
			return myPerson;
		}
		catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		
	}
	
	/**
	 * Assign person to a resource
	 * @param myPerson Object of the person
	 * @param resourceId resourceId of the person 
	 */
	public void assignPersonToResource(Person myPerson,int resourceId)
	{
		try {
			st.executeUpdate("update material set resourceId="+resourceId+" where id="+myPerson.getId()+"");
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void writeSelectAllEmployees() 
	{
		try {
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery("select * from person where type=1");
			while(rs.next())
			{
			   System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(4)+" "+rs.getInt(5)+" "+rs.getString(6)+" "+rs.getInt(7));	
			}
						
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
		}
	}
	
	public ArrayList<Person> selectbyResource(Resource myResource) 
	{
		try {
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery("select p.id,p.name,p.phone,p.address,p.salary,p.roleId,p.resourceId "+
											"from person p "+
											"join resource r on p.resourceId=r.id "+
											"where r.id="+myResource.getResourceId()+";");
			ArrayList<Person> myPersonList = new ArrayList<Person>();	
			if (rs.next()) {
			     do {
			    	 Person myPerson = new Person();
			    	 myPerson.setId(rs.getInt(1));
			    	 myPerson.setName(rs.getString(2));
			    	 myPerson.setPhone(rs.getString(3));
			    	 myPerson.setAddress(rs.getString(4));
			    	 myPerson.setSalary(rs.getInt(5));
			    	 myPerson.setRoleId(rs.getInt(6));
			    	 myPerson.setResourceId(rs.getInt(7));
			    	 myPersonList.add(myPerson);
			       } while (rs.next());
			 	return myPersonList;  
			     }
			  else {
			    return null;
		}			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}


/**
 * Getters and Setters
 * 
 */
	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}


	public String getPhone() {
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public Integer getSalary() {
		return salary;
	}

	public void setSalary(Integer salary) {
		this.salary = salary;
	}

	public Integer getType() {
		return type;
	}

	public void setType(Integer type) {
		this.type = type;
	}

	public Integer getRoleId() {
		return roleId;
	}

	public void setRoleId(Integer roleId) {
		this.roleId = roleId;
	}

	public Integer getResourceId() {
		return resourceId;
	}

	public void setResourceId(Integer resourceId) {
		this.resourceId = resourceId;
	}


	
	
}



